<?php
/**
 * Created by PhpStorm.
 * User: 4399-1500
 * Date: 2018/6/8
 * Time: 10:53
 */

namespace Common;
use Yaf\Registry;
use Yaf\Exception;

class PHPExcelLib
{

    private $_php_excel;        //excel 实例

    protected $_table_name;     //表名

    protected $_file_name;      //文件名

    protected $_index;

    private $__error = 0;

    //实例化PHPExcel
    public function __construct()
    {
        $this->_php_excel = new \PHPExcel();
        self::setDocPro();
    }

    public function getPHPExcel(){
        return $this->_php_excel;
    }

    public function setFileName($file_name = '')
    {
        if (empty($file_name)) $this->_file_name = date('Y-m-d H:i:s',time());
        $this->_file_name = $file_name;
        return $this;
    }

    //设置文档属性
    public function setDocPro(){
        $this->_php_excel->getProperties()
            ->setCreator("Stone Base")                              //创建人
            ->setLastModifiedBy("Stone Base")                       //最后修改人
            ->setTitle("Office 2007 Document")                      //标题
            ->setSubject("Office 2007 XLSX Test Document")          //题目
            ->setDescription("description")                         //描述
            ->setKeywords("office 2007 openxml php")                //关键字
            ->setCategory("Test result file");                      //种类
    }

    public function setTitleStype()
    {

        $this->_php_excel->getActiveSheet()->mergeCells('A1:'.'J1');//合并单元格（如果要拆分单元格是需要先合并再拆分的，否则程序会报错）
        $this->_php_excel->setActiveSheetIndex(0)->setCellValue('A1',$this->_file_name);
        $this->_php_excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $this->_php_excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);

        $this->_php_excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->_php_excel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        return $this;
    }

    public function setHeadTitl($cellName = [])
    {
        $topNumber = 2;//表头有几行占用
        $cellKey = array(
            'A','B','C','D','E','F','G','H','I','J','K','L','M',
            'N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
            'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM',
            'AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'
        );
        foreach ($cellName as $k=>$v)
        {
            $this->_php_excel->setActiveSheetIndex(0)->setCellValue($cellKey[$k].$topNumber, $v);//设置表头数据
            $this->_php_excel->getActiveSheet()->freezePane($cellKey[$k].($topNumber+1));//冻结窗口
            $this->_php_excel->getActiveSheet()->getStyle($cellKey[$k].$topNumber)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中
            if($v > 0)//大于0表示需要设置宽度
            {
                $this->_php_excel->getActiveSheet()->getColumnDimension($cellKey[$k])->setWidth($v);//设置列宽度
            }
        }
        return $this;
    }

    /**
     * 设置当前sheet 编号
     * @param int $index
     * @return $this
     */
    public function setSheetIndex($index = 0){

        $this->_index = $index;
        return $this;
    }

    /**
     * 设置当前sheet的名字
     * @param string $title
     * @return $this
     * @throws \PHPExcel_Exception
     */
    public function setSheetTitle($title = 'sheet'){
        $this->_php_excel->getActiveSheet()->setTitle($title);
        return $this;
    }

    /**
     * 添加数据
     * @param array $data
     * @return $this
     * @throws \PHPExcel_Exception
     */
    public function setCellValue($data = [])
    {
        if (empty($data) && !is_array($data)) new Exception('Data 数据不能为空，必须是二维数组');

        foreach ($data as $row => $dataInfo){
            $rowNum=$row+3;
            $cloNum='A';
            foreach($dataInfo as $key=>$value){
                $this->_php_excel->setActiveSheetIndex($this->_index)->setCellValue($cloNum.$rowNum,$value);
                $cloNum++;
            }
        }

        return $this;
    }

    /**
     * 下载文件
     * @return bool|string
     * @throws \PHPExcel_Reader_Exception
     */
    public function export(){


        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$this->_file_name.'.xlsx"');
        header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0

        $objWriter = \PHPExcel_IOFactory::createWriter($this->_php_excel, 'Excel2007');
        $objWriter->save('php://output');



        return $this->__error == 0 ? True : $this->__getResult();
    }



}